package com.chpro.common;


import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


/**
 * excel工具类
 * @author Administrator
 *
 */
public class ExcelUtils {
    private static Log log =  LogFactory.getLog(ExcelUtils.class);

    /**
     * 将一个List集合 转换 为excel 文件流
     * @param list 集合
     * @return 流
     */
    public static OutputStream getExcelByList(List<Map<String,Object>> list,OutputStream out){

        Workbook workbook = new HSSFWorkbook();

        String[] headers = Lang.getStringArray(list.get(0).keySet().toArray());

        List<List<String>> data = Lang.getListUtil(list,headers);


        ExcelUtils excelUtil = new ExcelUtils();
        try {
            excelUtil.exportExcel(workbook,0,"sheet1", headers,  data, out);
            workbook.write(out);
        } catch (Exception e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }

        return out;
    }

    /**
     * 将一个 excel文件流 转换成 List集合
     * @return List集合
     */
    public static List<Map<String,Object>> getListByExcel(InputStream stream)throws Exception{
        List<Map<String,Object>> sheetList = new ArrayList<Map<String,Object>>();

        List keyList = new ArrayList();

        POIFSFileSystem fs = new POIFSFileSystem(stream);
        // 创建工作簿

        HSSFWorkbook workBook = new HSSFWorkbook(fs);

        log.info("工作表个数 :"+workBook.getNumberOfSheets());

        for(int i=0;i<1;i++){
            log.info("工作表名称"+workBook.getSheetName(i));
            
            // 创建工作表
            HSSFSheet sheet = workBook.getSheetAt(i);
            int rows = sheet.getPhysicalNumberOfRows(); // 获得行数


            if(rows>1){	//如果行数大于0

                sheet.getMargin(HSSFSheet.TopMargin);

                //循环行数

                for(int j=0;j<rows;j++){
                    HSSFRow row = sheet.getRow(j);
                    if(row!=null){
                        //获得列的数据

                        int cells = row.getLastCellNum();//获得列数


                        Map rowMap = new LinkedHashMap();

                        //初始化　row Map

                        if(keyList.size()>0){
                            for(Object o:keyList){
                                rowMap.put(o.toString()," ");
                            }
                        }


                        //然后获得列的数据

                        for(int k=0;k<cells;k++){
                            HSSFCell cell = row.getCell(k);

                            if (cell != null) {
                                String value = "";
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC: // 数值型

                                        if (HSSFDateUtil.isCellDateFormatted(
                                                cell)) {
                                            //如果是date类型则 ，获取该cell的date值

                                            value = HSSFDateUtil.getJavaDate(
                                                    cell.getNumericCellValue()).
                                                    toString();
                                        }else{//纯数字


                                            value = String.valueOf(cell
                                                    .getNumericCellValue());
                                        }
                                        break;
	                                 /* 此行表示单元格的内容为string类型 */
                                    case HSSFCell.CELL_TYPE_STRING: // 字符串型

                                        value = cell.getRichStringCellValue()
                                                .toString();
                                        break;
                                    case HSSFCell.CELL_TYPE_FORMULA://公式型

                                        //读公式计算值

                                        value = String.valueOf(cell.getNumericCellValue());
                                        if(value.equals("NaN")){//如果获取的数据值为非法值,则转换为获取字符串


                                            value = cell.getRichStringCellValue().toString();
                                        }
                                        break;
                                    case HSSFCell.CELL_TYPE_BOOLEAN://布尔

                                        value = " "
                                                + cell.getBooleanCellValue();
                                        break;
	                                 /* 此行表示该单元格值为空 */
                                    case HSSFCell.CELL_TYPE_BLANK: // 空值

                                        value = "";
                                        break;
                                    case HSSFCell.CELL_TYPE_ERROR: // 故障

                                        value = "";
                                        break;
                                    default:
                                        value = cell.getRichStringCellValue().toString();
                                }

                                //把第一行放进 List key 里

                                if(j==0){
                                    keyList.add(value);
                                    continue;
                                }

                                if(k<keyList.size()){
                                    rowMap.put(keyList.get(k), value);
                                }else{
                                    log.info("此列没有标题");
                                }

                            }

                        }

                        //第一行不添加

                        if(j>0){
                            sheetList.add(rowMap);
                        }
                    }
                }
            }
        }

        stream.close();
        return sheetList;
    }

    /**
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: EX-WANGJIANQIANG001 @ 2012-8-31 下午03:49:08
     * @param workbook
     * @param sheetNum
     * @param sheetTitle
     * @param headers
     * @param result
     * @param out
     * @throws Exception
     */
    public void exportExcel(Workbook workbook,int sheetNum,String sheetTitle, String[] headers, List<List<String>> result, OutputStream out) throws Exception{
        // 生成一个表格

        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum,sheetTitle);
        // 生成一个样式

        CellStyle style = workbook.createCellStyle();
        // 设置样式

        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体

        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式

        style.setFont(font);

        // 指定当单元格内容显示不下时自动换行

        style.setWrapText(true);

        // 产生表格标题行

        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
        // 遍历集合数据，产生数据行

        if(result != null){
            int index = 1;
            for(List<String> m:result){
                row = sheet.createRow(index);
                int cellIndex = 0;
                for(Object str:m){
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(String.valueOf(str));
                    cellIndex++;
                }
                index++;
            }
        }
    }
}